package com.example.japinhands

import android.content.Context
import android.util.Log
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.example.japinhands.dao.AdjDao
import com.example.japinhands.dao.SentenceDao
import com.example.japinhands.dao.VerbDao
import com.example.japinhands.dao.WordDao
import com.example.japinhands.data.DeformAdj
import com.example.japinhands.data.DeformVerb
import com.example.japinhands.data.Sentence
import com.example.japinhands.data.Word
import com.example.japinhands.data.WordConstant

@Database(
    version = 4,
    entities = [
        Word::class,
        DeformVerb::class,
        DeformAdj::class,
        Sentence::class
    ]
)
abstract class WordDatabase : RoomDatabase() {

    abstract fun wordDao(): WordDao
    abstract fun adjDao(): AdjDao
    abstract fun verbDao(): VerbDao
    abstract fun sentenceDao(): SentenceDao

    companion object {
        private var instance: WordDatabase? = null

        private const val TAG = "WordDatabase"

        private const val dropTables =
            "drop table if exists sentence;" +
                    "drop table if exists deform_verb;" +
                    "drop table if exists deform_adj;" +
                    "drop table if exists verb;" +
                    "drop table if exists word;"

        private const val createTables =
            "create table if not exists sentence(" +
                    "id integer primary key autoincrement not null," +
                    "key integer not null," +
                    "content text not null" +
            ");" +
            "create table if not exists deform_verb(" +
                    "id integer primary key autoincrement not null," +
                    "deform1 text not null," +
                    "deform2 text not null," +
                    "deform3 text not null," +
                    "deform4 text not null," +
                    "deform5 text not null," +
                    "deform6 text not null," +
                    "deform7 text not null," +
                    "deform8 text not null," +
                    "deform9 text not null," +
                    "deform10 text not null," +
                    "deform11 text not null," +
                    "deform12 text not null," +
            ");" +
            "create table if not exists deform_adj(" +
                    "id integer primary key autoincrement not null," +
                    "deform1 text not null," +
                    "deform2 text not null," +
                    "deform3 text not null," +
                    "deform4 text not null," +
            ");" +
            "create table if not exists word(" +
                    "id integer primary key autoincrement not null," +
                    "kanji text not null" +
                    "gana text not null" +
                    "meaning text not null" +
                    "notice text not null" +
                    "type integer not null" +
                    "deform_verb integer not null" +
                    "deform_adj integer not null" +
                    "sentence_key integer not null" +
            ");"

        private const val createTables_2_3 =
            "create table if not exists sentence(" +
                    "id integer primary key autoincrement not null," +
                    "content text not null" +
                    ");" +
                    "create table if not exists deform_verb(" +
                    "id integer primary key autoincrement not null," +
                    "deform1 text not null," +
                    "deform2 text not null," +
                    "deform3 text not null," +
                    "deform4 text not null," +
                    "deform5 text not null," +
                    "deform6 text not null," +
                    "deform7 text not null," +
                    "deform8 text not null," +
                    "deform9 text not null," +
                    "deform10 text not null," +
                    "deform11 text not null," +
                    "deform12 text not null," +
                    ");" +
                    "create table if not exists deform_adj(" +
                    "id integer primary key autoincrement not null," +
                    "deform1 text not null," +
                    "deform2 text not null," +
                    "deform3 text not null," +
                    "deform4 text not null," +
                    ");" +
                    "create table if not exists word(" +
                    "id integer primary key autoincrement not null," +
                    "kanji text not null" +
                    "gana text not null" +
                    "meaning text not null" +
                    "notice text not null" +
                    "type integer not null" +
                    "deform_verb integer not null" +
                    "deform_adj integer not null" +
                    "sentence_key integer not null" +
                    ");"

        private const val createTables_3_4 =
            "create table if not exists deform_verb(" +
                    "id integer primary key autoincrement not null," +
                    "deform1 text not null," +
                    "deform2 text not null," +
                    "deform3 text not null," +
                    "deform4 text not null," +
                    "deform5 text not null," +
                    "deform6 text not null," +
                    "deform7 text not null," +
                    "deform8 text not null," +
                    "deform9 text not null," +
                    "deform10 text not null," +
                    "deform11 text not null," +
                    "deform12 text not null" +
                    ");" +
                    "create table if not exists deform_adj(" +
                    "id integer primary key autoincrement not null," +
                    "deform1 text not null," +
                    "deform2 text not null," +
                    "deform3 text not null," +
                    "deform4 text not null" +
                    ");" +
                    "create table if not exists word(" +
                    "id integer primary key autoincrement not null," +
                    "kanji text not null," +
                    "gana text not null," +
                    "meaning text not null," +
                    "notice text not null," +
                    "type integer not null," +
                    "deform_verb integer not null," +
                    "deform_adj integer not null," +
                    "sentence_content text not null" +
                    ");"

        private fun resetTables(database: SupportSQLiteDatabase) {
            database.execSQL(dropTables)
            database.execSQL(createTables)
        }

        private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                resetTables(database)
                WordConstant.saveNewKey(0)
                Log.d(TAG, "Migration_1_2, reset key: ${WordConstant.currSentenceKey}")
            }
        }

        private val MIGRATION_2_3 = object : Migration(2, 3) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(dropTables)
                database.execSQL(createTables_2_3)
            }
        }

        private val MIGRATION_3_4 = object : Migration(3, 4) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.execSQL(dropTables)
                database.execSQL(createTables_3_4)
            }
        }

        @Synchronized
        fun getDatabase(context: Context): WordDatabase {
            instance?.let {
                return it
            }
            /**
             * fallbackToDestructiveMigration()
             *      Destroy the database with its tables with data on upgrade,
             *      so that we can easily make changes to the structure of the
             *      new tables.
             */
            return Room.databaseBuilder(
                context.applicationContext,
                WordDatabase::class.java,
                "word_database"
            ).addMigrations(
                MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4
            ).build().apply {
                instance = this
            }
        }
    }
}